﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
/// <summary>
///DataProvider 的摘要说明
/// </summary>
public class MysqlHelp
{
    MySqlConnection con;
    /// <summary>
    /// 
    /// </summary>
    public MysqlHelp()
    {
        string sqlstring = System.Configuration.ConfigurationManager.AppSettings["sqlconn"];
        con = new MySqlConnection(sqlstring);
    }
    /// <summary>
    /// 数据连接
    /// </summary>
    /// <param name="ip"></param>
    /// <param name="database"></param>
    /// <param name="username"></param>
    /// <param name="pwd"></param>
    public MysqlHelp(string ip, string database, string username, string pwd)
    {
        string sql = "Server=" + ip + ";UserName=" + username + ";Password=" + pwd + ";Database=" + database + ";Port=3306;CharSet=utf8;Allow Zero Datetime=true";
        con = new MySqlConnection(sql);
    }

    #region SQL语句操作
    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int Runsql(string sql)
    {
        int i = -1;
        MySqlCommand com = new MySqlCommand(sql, con);
        try
        {
            con.Open();
            i = com.ExecuteNonQuery();
        }
      catch(Exception e)
        {
            Console.WriteLine(e);
         }
        con.Close();
        return i;
    }
    /// <summary>
    /// 执行带参数SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public int Runsql(string sql, IDataParameter[] parms)
    {
        int i = -1;
        MySqlCommand com = new MySqlCommand(sql, con);
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        try
        {
            con.Open();
            i = com.ExecuteNonQuery();
        }
        catch(Exception e)
        {
            Console.WriteLine(e);
        }
        
        con.Close();
        return i;
    }

    /// <summary>
    /// 执行SQL语句获得DATATABLE
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable GetDatabysql(string sql)
    {
        DataSet ds = new DataSet();
        try
        {
            con.Open();
            MySqlDataAdapter da = new MySqlDataAdapter(sql, con);
            da.Fill(ds);
        }
        catch(Exception e)
         {
             Console.WriteLine(e);
        }
        con.Close();
        return ds.Tables[0];
    }
    /// <summary>
    /// 执行带参数SQL语句获得DATATABLE
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public DataTable GetDatabysql(string sql, IDataParameter[] parms)
    {
        DataSet ds = new DataSet();
        con.Open();
        MySqlCommand com = new MySqlCommand(sql, con);
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        MySqlDataAdapter da = new MySqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }
    #endregion

    #region 操作存储过程
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="prcname"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public int RunPrc(string prcname, IDataParameter[] parms)
    {
        int i = -1;
        MySqlCommand com = new MySqlCommand(prcname, con);
        com.CommandType = CommandType.StoredProcedure;
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        con.Open();
        i = com.ExecuteNonQuery();
        con.Close();
        return i;
    }

    /// <summary>
    /// 执行存储过程获得数据集
    /// </summary>
    /// <param name="prcname"></param>
    /// <returns></returns>
    public DataTable GetDataByPrc(string prcname)
    {
        DataSet ds = new DataSet();
        MySqlCommand com = new MySqlCommand(prcname, con);
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        MySqlDataAdapter da = new MySqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }
    /// <summary>
    /// 执行存储过程获得数据集(带参数)
    /// </summary>
    /// <param name="prcname"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public DataTable GetDataByPrc(string prcname, IDataParameter[] parms)
    {
        DataSet ds = new DataSet();
        con.Open();
        MySqlCommand com = new MySqlCommand(prcname, con);
        com.CommandType = CommandType.StoredProcedure;
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        MySqlDataAdapter da = new MySqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }
    #endregion
}

